import traceback

import pymysql
import baostock as bs
import pandas as pd
from decimal import Decimal
import math
import datetime

def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        pass
    return False


lg = bs.login(user_id='anonymous', password='123456')
# 打开数据库连接
db = pymysql.connect(host='101.35.200.120', port=3306, user='root', passwd='ljf123456', db='gp',charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL 查询语句
sql = "select * from shares"
try:
 # 执行SQL语句
 cursor.execute(sql)
 # 获取所有记录列表
 results = cursor.fetchall()
 for row in results:
      id = row[0]
      code = row[1]
      name=row[2]
      regionCode = row[5]
      #获取最后日期 ALTER TABLE trade_history ADD INDEX index_name ( time )
      sSql = """select id, time_val from record_data_%s  where  code = '%s'  order by time desc limit 1 """ \
                  % (code,code )
      #print(sSql)
      cursor.execute(sSql)
      sResults = cursor.fetchall()
      #print(sSql)
      if (len(sResults) > 0):

          for row in sResults:
              #data = ((datetime.datetime.strptime(row[2], '%Y-%m-%d')).timedelta(days=1)).strftime('%Y-%m-%d')
              in_date = row[1]
              if(in_date == None):
                  data = '1990-01-01'
              else:
                dt = datetime.datetime.strptime(in_date, "%Y-%m-%d")
                #if(dt>datetime.datetime.now()):
                data = (dt + datetime.timedelta(days=1)).strftime("%Y-%m-%d")

      else:
          data='1990-01-01'
      #print(data)
      if (datetime.datetime.strptime(data, "%Y-%m-%d") <= datetime.datetime.now()):
         rs = bs.query_history_k_data_plus(regionCode,
                                        "date,code,open,high,low,close,preclose,volume,amount,adjustflag,turn,tradestatus,pctChg,peTTM,pbMRQ,psTTM,pcfNcfTTM,isST",
                                        start_date=data, end_date='%s-%s-%s' % (datetime.datetime.now().year, datetime.datetime.now().month,datetime.datetime.now().day),
                                        frequency="d",
                                        adjustflag="3")
         #frequency="d"取日k线，adjustflag="3"复权类型，默认不复权：3；1：后复权；2：前复权
         #print('query_history_k_data_plus respond error_code:' + rs.error_code)
         #print('query_history_k_data_plus respond  error_msg:' + rs.error_msg)
        #### 打印结果集 ####
         data_list = []
         while (rs.error_code == '0') & rs.next():
          li=rs.get_row_data()
          openingPrice=li[2];
          openingPrice = openingPrice if is_number(openingPrice) else 0
          #is_number(openingPrice)
          #openingPrice=is_number(openingPrice)==True?openingPrice:0;
          maxPrice=li[3];
          maxPrice = maxPrice if is_number(maxPrice) else 0
          minPrice=li[4];
          minPrice = minPrice if is_number(minPrice) else 0
          closePrice=li[5];
          closePrice = closePrice if is_number(closePrice) else 0
          preclose=li[6];
          preclose = preclose if is_number(preclose) else 0
          turnover=li[7];
          turnover = turnover if is_number(turnover) else 0
          turnover = float(turnover)
          turnover = math.trunc(turnover)
          turnoverAmount=li[8];
          turnoverAmount =turnoverAmount if is_number(turnoverAmount) else 0
          turnoverAmount = float(turnoverAmount)
          turnoverAmount = math.trunc(turnoverAmount)
          turnoverRate=li[10];
          turnoverRate =turnoverRate if is_number(turnoverRate) else 0
          tradestatus=li[11];
          tradestatus =tradestatus  if is_number(tradestatus) else 0
          todayRange=li[12];
          todayRange =todayRange if is_number(todayRange) else 0
          peTtm = li[13];
          peTtm =peTtm if is_number(peTtm) else 0
          pbMrq=li[14];
          pbMrq =pbMrq if is_number(pbMrq) else 0
          psTtm=li[15];
          psTtm =psTtm  if is_number(psTtm) else 0
          pcfNcfTtm=li[16];
          pcfNcfTtm =pcfNcfTtm if is_number(pcfNcfTtm) else 0
          isSt=li[17];
          isSt =isSt if is_number(isSt) else 0


          #amountVal = li[8].rstrip('0').rstrip('.')
          #turnoverVal = li[7].rstrip('0').rstrip('.')
          #print(li
          sql1 = """INSERT INTO record_data_%s (time,time_val, region_code, opening_price, max_price, min_price, close_price, preclose, turnover,
                        turnover_amount, turnover_rate, tradestatus, today_range, pe_ttm, pb_mrq, ps_ttm, pcf_ncf_ttm,
                         is_st,name,code) VALUES ('%s','%s', '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') """ \
                 %(code,li[0],li[0], li[1], openingPrice, maxPrice, minPrice,closePrice,preclose, turnover,turnoverAmount,turnoverRate, tradestatus, todayRange, peTtm,pbMrq, psTtm,pcfNcfTtm,isSt,name,code)
          try:
              #查重
              #selectSql = """select * from trade_history  WHERE  code = '%s'  AND time = '%s' """ \
              #            % (code, li[0])
              #cursor.execute(selectSql)
              #print(selectSql)
              #selectResults = cursor.fetchall()
              #if (len(selectResults) == 0):
              print(sql1)
              cursor.execute(sql1)
              # 执行sql语句
              db.commit()
          except Exception as e:
              print(sql1)
              #f = open('D:/except.txt', 'a')
              #f.write('\n'+sql1)
              # 发生错误时回滚
              db.rollback()




          # 获取一条记录，将记录合并在一起
          #data_list.append(rs.get_row_data())
      #result = pd.DataFrame(data_list, columns=rs.fields)
      #### 结果集输出到csv文件 ####
      #result.to_csv("D:/history_k_data.csv", encoding="gbk", index=False)






      # 打印结果
      #print ("id=%s,code=%s,name=%s,regionCode=%s" % \
      #       (id, code, name,  regionCode ))
except Exception as e:
   traceback.print_exc()
   print ("Error: unable to fecth data")

# 关闭数据库连接
db.close()
# 登出系统
bs.logout()






